﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;

/// <summary>
/// Summary description for MyConnection
/// </summary>
public class MyConnection
{
    #region "variable"
    private string _strConn = @"server=THEWINNER-PC\SQL2012;database=Mobile;uid=sa;pwd=123";            
    private string _error;    
    #endregion

    #region "properties"
    public string StrConn
    {
        get { return _strConn; }
        set { _strConn = value; }
    }
    public string Error
    {
        get { return _error; }
        set { _error = value; }
    }
    #endregion

    public MyConnection()
	{        
        Error = "";
	}


    public object GetValue(string spName,IDataParameter[] parameters)
    {
        SqlConnection conn = new SqlConnection(_strConn);
        object result=null;
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand comm = new SqlCommand();
            comm = this.AddParameter(spName, parameters,conn);
            
            result = comm.ExecuteScalar();        
           
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return result;
    }
    
    public DataTable GetDataTable(string strSQL,int type)
    {
        SqlConnection conn = new SqlConnection(_strConn);
        DataTable dt = new DataTable();
        try
        {

            SqlCommand comm = new SqlCommand();
            comm.Connection = conn;
            switch (type)
            {
                case 1:
                    comm.CommandType = CommandType.StoredProcedure;
                    break;
                case 2:
                    comm.CommandType = CommandType.Text;
                    break;
            }        
            comm.CommandText = strSQL;        
            SqlDataAdapter da = new SqlDataAdapter(comm);
            
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(dt);        
            
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return dt;
    }
    
    public DataTable GetDataTable(string spName,IDataParameter[] parameters)
    {
        SqlConnection conn = new SqlConnection(_strConn);
        DataTable dt = new DataTable();
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand comm = new SqlCommand();
            comm = this.AddParameter(spName, parameters,conn);        
            SqlDataAdapter da = new SqlDataAdapter(comm);
            
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
            da.Fill(dt);        
            
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
        return dt;
    }
    
    public int ExecuteNonQuery(string spName,IDataParameter[] parameters)
    {
        SqlConnection conn = new SqlConnection(_strConn);
        try
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            SqlCommand comm = new SqlCommand();
            comm = this.AddParameter(spName, parameters,conn);
            return comm.ExecuteNonQuery();
        }
        finally
        {
            conn.Close();
            conn.Dispose();
        }
    }
    
    public SqlCommand AddParameter(string spName,IDataParameter[] parameters,SqlConnection conn)
    {                
        SqlCommand comm = new SqlCommand();
        comm = new SqlCommand();
        comm.Connection = conn;
        comm.CommandType = CommandType.StoredProcedure;
        comm.CommandText = spName;
        foreach (SqlParameter item in parameters)
        {
            comm.Parameters.Add(item);
        }
        return comm;                
    }
}